﻿using System;
using System.Collections.Generic;
using System.Diagnostics.CodeAnalysis;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading;
using DimensionsHelper.Common.Contracts.Excel;
using DimensionsHelper.Common.Contracts.Metadata;
using DimensionsHelper.Common.Contracts.Services;
using DimensionsHelper.Common.Contracts.Sql;
using DimensionsHelper.Common.Metadata.Script;
using DimensionsHelper.Common.Sql;
using DimensionsHelper.Common.Utils;
using DimensionsHelper.Services.DataTable;
using DimensionsHelper.Services.Metadata;

namespace DimensionsHelper.Services.SQL;

/// <summary>
///     从Excel读取原始数据并格式化成INSERT SQL脚本语句。
/// </summary>
/// <remarks>
///     头部行视为字段名，如果第一行出现空白单元格，将第二行视为下级字段名。
///     对于不同类型的单元格数据，需要根据不同的数据分类输出值。
///     <list type="number">
///         <item>
///             检索字段是否是单值字段，如果是单值字段，再判断当前值是否是有效值。
///         </item>
///         <item>
///             检索字段名是否在配置中配置成了分类名映射，如果配置了，检索是否是忽略分类前缀的分类名。
///         </item>
///         <item>
///             其余情况视为数值类型或字符串类型，使用原始字符串。
///         </item>
///     </list>
/// </remarks>
public class InsertSqlBuilder : IInsertSqlBuilder
{
    private const string ExportLabelType = "Label";
    private const string ExportContext = "Analysis";
    private const string ExportLanguage = "ZH-CN";
    private const string SqlFormat = "INSERT vdata ({0}) VALUES ({1})";

    private readonly List<string> _buffer = [];


    private readonly List<ColumnInfo> _sheetColumnInfos = [];
    private readonly RegexManager _tableFieldNamesExpr = new() { Exactly = true, IgnoreCase = true };
    private readonly List<SampleDataTable> _tables = [];
    private string _currentExcelPath = string.Empty;
    private bool _isSampleDataTablePrepared;

    private MetadataDocument? _metadata;
    private Parser? _parser;
    private SqlMetadataProvider? _provider;

    private SqlMetadataProvider Provider => _provider!;

    private Parser Parser => _parser!;

    public string Section => "SqlBuilder";

    public IFilePikerService? FilePikerService { get; set; }

    public IReportService? ReportService { get; set; }

    public MetadataCodeMap Settings { get; private set; } = new();

    public bool FillSampleDataTable { get; set; }

    public bool AutoMergeList { get; set; } = false;


    public void Clear()
    {
        _buffer.Clear();
        _tables.Clear();
        _metadata?.Close();
        _metadata = null;
        _provider?.Clear();
        _currentExcelPath = string.Empty;
        Expression.ResetMetadataProvider();
    }

    public bool LoadSettings(MetadataCodeMap settings)
    {
        settings.Refresh();
        Settings = settings;
        return ValidateCodeMap();
    }


    public bool LoadSettings(object obj)
    {
        if (obj is MetadataCodeMap codeMap)
        {
            return LoadSettings(codeMap);
        }

        return false;
    }


    public bool LoadSettings(string settingFileName)
    {
        if (!File.Exists(settingFileName))
        {
            ReportService?.Error($"文件'{settingFileName}'不存在。");
            return false;
        }

        var extension = Path.GetExtension(settingFileName).ToLower();

        if (extension != ".json" && extension != ".jsonc")
        {
            ReportService?.Error("只支持扩展名为'.json'或'.jsonc'的配置文件。");
            return false;
        }

        try
        {
            var map =
                JsonHelper.ReadJson(settingFileName, ServiceJsonContext.Default.MetadataCodeMap);

            if (map == null)
            {
                return true;
            }

            map.Refresh();
            Settings = map;
            return ValidateCodeMap();
        }
        catch (Exception e)
        {
            ReportService?.Error($"JSON解析错误：{e.Message}");
            return false;
        }
    }

    public void Export(string filePath)
    {
        if (_buffer.Count == 0)
        {
            ReportService?.Error("未找到已读取的有效字段。");
            return;
        }

        ReportService?.Log($"开始输出文件'{filePath}'。");

        var linesCount = _buffer.Count;
        double count = 1;

        using var writer = File.CreateText(filePath);
        foreach (var item in _buffer)
        {
            ReportService?.Progress(count / linesCount * 100, string.Empty);
            writer.WriteLine(item);
            count++;
        }

        ReportService?.Log($"目标文件'{filePath}'已输出完成。");

        ReportService?.Directory(Path.GetDirectoryName(filePath) ?? "", "SQL文件根路径");
        ReportService?.Directory(filePath, "SQL文件路径");
    }


    public void ReadExcel(SqlBuilderInputFileInfo fileInfo, CancellationToken cancellationToken)
    {
        ReadExcel(fileInfo, true, cancellationToken);
    }


    public void ReadExcel(SqlBuilderInputFileInfo fileInfo)
    {
        ReadExcel(fileInfo, true, CancellationToken.None);
    }


    public void ReadExcel(IEnumerable<SqlBuilderInputFileInfo> files, CancellationToken cancellationToken)
    {
        _buffer.Clear();
        PrepareSampleDataTables();

        foreach (var file in files)
        {
            if (cancellationToken.IsCancellationRequested)
            {
                return;
            }

            ReadExcel(file, false, cancellationToken);
        }

        _isSampleDataTablePrepared = false;
    }

    public void ReadExcel(IEnumerable<SqlBuilderInputFileInfo> files)
    {
        ReadExcel(files, CancellationToken.None);
    }


    public void ExportDataTableToExcel(IExcelWorkbookOperator workbook)
    {
        if (!FillSampleDataTable)
        {
            return;
        }

        if (_tables.Count == 0)
        {
            ReportService?.Warn("当前已读取数据为空。");
            return;
        }

        try
        {
            var worksheet = workbook.GetWorksheet("INSERT DATA");

            var row = 2;
            foreach (var table in _tables)
            {
                table.PrependBase();
                table.AppendSubtotal();
                table.WriteToExcel(worksheet, row, 2);
                row += table.Rows + 2;
            }

            worksheet.AutoFitEntireColumns();

            ReportService?.Directory(Path.GetDirectoryName(workbook.FileName) ?? "", "结果文件根路径");
            ReportService?.Directory(workbook.FileName, "结果文件路径");
        }
        catch (Exception e)
        {
            ReportService?.Error($"错误：{e.Message}。");
        }
        finally
        {
            workbook.Close(false);
        }
    }


    public void ExportMetadata(string filePath)
    {
        if (string.IsNullOrEmpty(filePath))
        {
            throw new FileNotFoundException();
        }

        PrepareMetadata();

        var serializer = new MetadataSerializer
        {
            Options =
            {
                IgnoreMetadataSection = true,
                Context = ExportContext,
                Language = ExportLanguage,
                LabelType = ExportLabelType,
                Indent = 4
            }
        };

        var content = serializer.Serialize(_metadata!);

        if (File.Exists(filePath))
        {
            File.Delete(filePath);
        }

        using var fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write);
        using var streamWriter = new StreamWriter(fileStream);
        streamWriter.WriteLine(content);

        ReportService?.Directory(Path.GetDirectoryName(filePath) ?? "", "脚本文件根路径");
        ReportService?.Directory(filePath, "脚本文件路径");
    }

    private bool ValidateCodeMap()
    {
        var appendNames = new HashSet<string>();

        foreach (var field in Settings.AppendFields)
        {
            if (string.IsNullOrEmpty(field.Name))
            {
                ReportService?.Error("'appendFields'中的字段名'name'配置不能是空字符串。");
                return false;
            }

            var lower = field.Name.ToLower();

            if (appendNames.Add(lower))
            {
                continue;
            }

            ReportService?.Error($"'appendFields'中字段名'{field.Name}'重复。");
            return false;
        }

        return true;
    }

    private void PrepareSampleDataTables()
    {
        PrepareMetadata();

        if (!FillSampleDataTable || Settings.CodeMapping.Count == 0 || _isSampleDataTablePrepared)
        {
            return;
        }

        var keyFieldName = Settings.KeyField.Trim();

        if (!string.IsNullOrEmpty(keyFieldName))
        {
            Provider.KeyFieldName = keyFieldName;
        }

        ReportService?.Log("开始预创建样本表 ...");

        _tables.Clear();
        _tableFieldNamesExpr.Clear();

        foreach (var item in Settings.CodeMapping)
        {
            if (item.IsLong || item.IsText)
            {
                continue;
            }

            var finalFields = new List<string>();
            var table = new SampleDataTable();

            foreach (var field in item.Fields)
            {
                var finalFieldName = Settings.GetField(field, out _);
                table.GetColumn(finalFieldName);
                finalFields.Add(finalFieldName);
            }

            table.AppendRows(item.Mapping.Keys);
            _tableFieldNamesExpr.Append(finalFields, $"T{_tables.Count}");
            _tables.Add(table);
        }

        foreach (var item in Settings.AppendFields)
        {
            if (string.IsNullOrEmpty(item.Name) ||
                item.Type != MetadataSqlFieldValueType.Categorical ||
                item.CodeMapping.Count == 0)
            {
                continue;
            }

            var table = new SampleDataTable();
            table.AppendRows(item.CodeMapping.Values);
            _tableFieldNamesExpr.Append(item.Name, $"T{_tables.Count}");
            _tables.Add(table);
        }

        ReportService?.Log("预创建样本表完成。");

        _isSampleDataTablePrepared = true;
    }

    private void IncreaseDataTableSample(object sampleId, IEnumerable<SampleValueInfo> infos)
    {
        if (!FillSampleDataTable)
        {
            return;
        }

        foreach (var info in infos)
        {
            if (string.IsNullOrEmpty(info.Field))
            {
                continue;
            }

            var searchIndex = _tableFieldNamesExpr.SearchIndex(info.Field);

            if (searchIndex < 0 || searchIndex >= _tables.Count)
            {
                continue;
            }

            var table = _tables[searchIndex];
            foreach (var val in info.OriginalValues.Where(val => !string.IsNullOrEmpty(val)))
            {
                table[val][info.Field].IncreaseSample(sampleId);
            }
        }
    }


    private string GetAppendFieldValue(MetadataSqlAppendField appendField, string value)
    {
        switch (appendField.Type)
        {
            case MetadataSqlFieldValueType.Text:
                return "\"" + value + "\"";

            case MetadataSqlFieldValueType.Categorical:
                if (appendField.IsFixed)
                {
                    return "{" + value + "}";
                }

                return Settings.GetCode(value);

            case MetadataSqlFieldValueType.Long:
            default:

                if (!double.TryParse(value, out _))
                {
                    throw new Exception("'appendField'已配置为'number'类型，'value'属性为无效数字。");
                }

                return value;
        }
    }


    private void JoinCode(string fieldName, string codes, IList<string>? originalValues = null)
    {
        try
        {
            PrepareMetadata();
            Provider.GetLastRecord().SetFieldValue(fieldName, codes, true, originalValues);
        }
        catch (Exception ex)
        {
            ReportService?.Error(ex.Message);
        }
    }

    private string UpdateSubFieldName(IDictionary<string, string> map, string cellText)
    {
        if (map.Count <= 0)
        {
            return cellText;
        }

        if (map.TryGetValue(cellText, out var mappedSubField))
        {
            // 映射表中配置的值都视作 Categorical 分类值。
            return Settings.GetCode(mappedSubField);
        }

        ReportService?.Warn($"二级字段'{cellText}'未配置映射字段，已跳过。");
        return string.Empty;
    }


    private void PrepareColumnInfos(IExcelWorksheetOperator sheet)
    {
        _sheetColumnInfos.Clear();

        // 当前的分级字段名，IMetadataCodeMapElement对象允许多个字段共享同一种码表配置，
        // 因此，此处必须记录准确的字段名。
        MetadataCodeMapElement? leveledElement = null;
        for (var col = 1; col <= sheet.EndColumn; col++)
        {
            var field = sheet.GetCellText(1, col).Trim();
            var subField = string.Empty;

            var isFieldEmpty = string.IsNullOrEmpty(field);
            var isSubFieldEmpty = true;

            // 当起始行大于2时，检查第2行是否包含下级字段名
            // +------------+--------------+
            // |  Field     | maybe empty  |
            // +------------+--------------+
            // | Sub Field  |  Sub Field   |
            // +------------+--------------+
            if (Settings.StartRow > 2)
            {
                subField = sheet.GetCellText(2, col).Trim();
                isSubFieldEmpty = string.IsNullOrEmpty(subField);
            }

            // 忽略空白字段
            if (isFieldEmpty && isSubFieldEmpty)
            {
                continue;
            }

            // 当SubField不是空时，要求leveledFieldName和isFieldEmpty不能同时为空。
            if (leveledElement == null && isFieldEmpty)
            {
                ReportService?.Warn($"字段'{subField}'匹配失败：没有匹配的上级字段。");
                continue;
            }

            // 跳过忽略的字段
            if (!isFieldEmpty && Settings.IsFieldIgnored(field))
            {
                continue;
            }

            MetadataCodeMapElement? element = null;
            var subFieldName = string.Empty;
            var isSingle = false;

            // 优先检查单值字段，同时，单值字段不支持嵌套字段。
            if (isSubFieldEmpty && Settings.IsSingleCodeField(field))
            {
                isSingle = true;
            }
            // 当前字段单元格不为空时，尝试查找配置的码表对象
            else if (!isFieldEmpty)
            {
                if (Settings.TryGetElement(field, out element))
                {
                    leveledElement = element;
                }
                else
                {
                    ReportService?.Warn($"字段'{field}'未找到配置，已跳过。");
                    continue;
                }

                // 检查二级字段
                if (!isSubFieldEmpty)
                {
                    // 如果配置了次级字段映射表，当未查找到配置的映射值时，跳过当前列。
                    subFieldName = UpdateSubFieldName(leveledElement.SubFieldsMapping, subField);
                    if (string.IsNullOrEmpty(subField))
                    {
                        continue;
                    }
                }
            }
            // 当前字段为空时，第二行必定不为空，此时判定为分级字段的下级字段
            else if (leveledElement != null)
            {
                subFieldName = UpdateSubFieldName(leveledElement.SubFieldsMapping, subField);
                if (string.IsNullOrEmpty(subFieldName))
                {
                    continue;
                }

                element = leveledElement;
            }

            _sheetColumnInfos.Add(new ColumnInfo(col, field, element)
            {
                IsSingleCode = isSingle,
                SubFieldName = subFieldName
            });
        }
    }

    private void ReadCell(IExcelWorksheetOperator sheet, int row, ColumnInfo colInfo)
    {
        var cellText = sheet.GetCellText(row, colInfo.Column).Trim();
        // 忽略空白行和错误字符内容
        if (string.IsNullOrEmpty(cellText) || StringHelper.IsExcelError(cellText))
        {
            return;
        }

        var isCategorical = false;
        // 先视作字符串
        var value = "\"" + cellText + "\"";
        List<string>? originals = null;
        // 单值字段
        if (colInfo.IsSingleCode)
        {
            // 优先检查映射表
            if (Settings.TryGetSingleCode(colInfo.FieldName, out var mapped))
            {
                value = Settings.GetCode(mapped);
                isCategorical = true;
            }
            // 此优先使用全局映射码号
            else if (!string.IsNullOrEmpty(Settings.SingleCodeReflect))
            {
                value = Settings.GetCode(Settings.SingleCodeReflect);
                isCategorical = true;
            }
        }
        // 多层字段和非多层字段可以合并处理。
        else if (!string.IsNullOrEmpty(cellText))
        {
            if (colInfo.MappedElement != null &&
                colInfo.MappedElement.TryGetMappingCode(cellText, out var leveledFieldCode, out originals))
            {
                value = Settings.GetCode(leveledFieldCode);
                isCategorical = true;
            }
            else if (double.TryParse(cellText, out var d))
            {
                value = d.ToString(CultureInfo.InvariantCulture);
            }
        }

        // 检查映射后的字段是否已存在，如果已存在，需要进行合并，合并只支持码号数据。
        var finalField = Settings.GetField(colInfo.FieldName, out _);
        if (!string.IsNullOrEmpty(colInfo.SubFieldName)) // subField 不是空。
        {
            finalField += "[" + colInfo.SubFieldName + "]." + Settings.SubFieldName;
        }

        if (isCategorical)
        {
            JoinCode(finalField, value, originals);

            if (colInfo.FieldName.Equals(Settings.KeyField, StringComparison.OrdinalIgnoreCase) ||
                finalField.Equals(Settings.KeyField, StringComparison.OrdinalIgnoreCase))
            {
                ReportService?.Warn("样本关键字字段不支持 Categorical 类型，已取消填充样本数据表配置。");
                FillSampleDataTable = false;
            }
        }
        else
        {
            Provider.GetLastRecord().SetFieldValue(finalField, value, false);
        }
    }

    private bool EvaluateAppendFieldValue(MetadataSqlAppendFieldValue val)
    {
        // 检查文件名筛选
        if (val.FileNames.Count > 0 && !val.ContainsFile(_currentExcelPath))
        {
            return false;
        }

        // 计算表达式
        if (string.IsNullOrEmpty(val.Expression))
        {
            return true;
        }

        bool eval;
        try
        {
            val.ParsedExpression ??= Parser.Parse(val.Expression);
            eval = (bool)val.ParsedExpression.Evaluate().GetValue(BasicTypes.Boolean);
        }
        catch (Exception e)
        {
            eval = false;
            ReportService?.Warn(e.Message);
        }

        return eval;
    }


    private void AppendField(MetadataSqlAppendField field)
    {
        foreach (var val in field.Values)
        {
            if (!EvaluateAppendFieldValue(val))
            {
                continue;
            }

            var finalValue = GetAppendFieldValue(field, val.Value);

            if (field.Type == MetadataSqlFieldValueType.Categorical)
            {
                var labels = new List<string>();

                if (field.CodeMapping.TryGetValue(val.Value, out var label))
                {
                    labels.Add(label);
                }

                JoinCode(field.Name, finalValue, labels);
            }
            else
            {
                Provider.GetLastRecord().SetFieldValue(field.Name, finalValue, false);
            }
        }
    }


    private void ReadExcel(IExcelWorksheetOperator worksheet, CancellationToken cancellationToken)
    {
        PrepareMetadata();
        Provider.Clear();
        PrepareColumnInfos(worksheet);

        for (var r = Settings.StartRow; r <= worksheet.EndRow; r++)
        {
            foreach (var col in _sheetColumnInfos)
            {
                if (cancellationToken.IsCancellationRequested)
                {
                    return;
                }

                ReadCell(worksheet, r, col);
            }

            // appendFields
            foreach (var field in Settings.AppendFields)
            {
                AppendField(field);
            }

            if (FillSampleDataTable)
            {
                var keyFieldValue = Provider.GetLastRecord().KeyValue;

                if (!string.IsNullOrEmpty(keyFieldValue) && Provider.LastRecord != null)
                {
                    IncreaseDataTableSample(keyFieldValue, Provider.LastRecord.GetSampleValueInfos());
                }
            }

            if (Provider.FirstRecord != null)
            {
                _buffer.Add(Provider.PopSqlScript());
            }
        }
    }

    private void ReadExcel(SqlBuilderInputFileInfo fileInfo, bool clear, CancellationToken cancellationToken)
    {
        if (!fileInfo.Operator.IsInitialized)
        {
            fileInfo.Operator.Open(fileInfo.FileName);
        }

        if (Settings.CodeMapping.Count == 0)
        {
            ReportService?.Log("未配置有效的码表配置，将自动配置。");

            try
            {
                Settings = MetadataCodeMap.FromExcelWorkbook(fileInfo.Operator);
            }
            catch (Exception e)
            {
                ReportService?.Error(e.Message);
                return;
            }
        }

        if (clear)
        {
            _buffer.Clear();
        }

        foreach (var worksheet in fileInfo.Operator.Worksheets)
        {
            if (cancellationToken.IsCancellationRequested)
            {
                return;
            }

            ReportService?.Log($"开始读取工作表：{worksheet.Name}。");

            if (worksheet.EndRow < 2 || worksheet.EndColumn < 1)
            {
                ReportService?.Log($"工作表{worksheet.Name}行或列过少，已跳过。");
                continue;
            }

            ReadExcel(worksheet, cancellationToken);

            ReportService?.Log($"工作表'{worksheet.Name}'读取已完成。");
        }
    }

    /// <summary>
    ///     向指定路径输出一个空白的配置文件。
    /// </summary>
    /// <param name="fileName"></param>
    /// <returns></returns>
    public static bool ExportSettingTemplate(string fileName)
    {
        try
        {
            JsonHelper.WriteJson(new MetadataCodeMap(),
                ServiceJsonContext.Default.MetadataCodeMap,
                fileName);
        }
        catch (Exception)
        {
            return false;
        }

        return true;
    }

    private void InitializeElements(IElements elements, IDictionary<string, string> mapping)
    {
        foreach (var pair in mapping)
        {
            var element = elements.AddNew(Settings.GetCode(pair.Value, false));
            element.Label.SetText(ExportLabelType, ExportContext, ExportLanguage, pair.Key);
        }
    }

    private void CreateMetadata(MetadataDocument document)
    {
        foreach (var item in Settings.CodeMapping)
        {
            if (item.IsText || item.IsLong || item.IsDouble)
            {
                foreach (var textFieldName in item.Fields)
                {
                    document.CreateField(textFieldName,
                        item.IsText ? FieldDataType.Text : item.IsLong ? FieldDataType.Long : FieldDataType.Double);
                }

                continue;
            }

            var loopFieldRangeMap = new Dictionary<string, MetadataFieldRangeInfo>();
            var fieldMap = new Dictionary<string, IField>();

            foreach (var field in item.Fields)
            {
                var fieldName = Settings.GetField(field, out var replaceValue);
                var lbIndex = fieldName.IndexOf('[');
                var rbIndex = fieldName.IndexOf(']');

                var isNumericLoop = item.IsLong;
                var innerIndex = 0;
                // loop field
                if (lbIndex >= 0 && rbIndex >= 0)
                {
                    if (rbIndex - lbIndex < 2 || lbIndex == 0 || rbIndex >= fieldName.Length - 2)
                    {
                        ReportService?.Error($"变量名'{fieldName}'无效。");
                        continue;
                    }

                    if (string.IsNullOrEmpty(replaceValue))
                    {
                        replaceValue = fieldName.Substring(lbIndex + 1, rbIndex - lbIndex);

                        if (int.TryParse(replaceValue, out innerIndex))
                        {
                            isNumericLoop = true;
                        }
                        else if (replaceValue.Length > 2 &&
                                 replaceValue[0] == '{' &&
                                 replaceValue[^1] == '}')
                        {
                            replaceValue = replaceValue.Substring(1, replaceValue.Length - 2);
                        }
                        else
                        {
                            ReportService?.Error($"变量名'{fieldName}'无效。");
                            continue;
                        }
                    }

                    // 获取或创建循环变量
                    var loopName = fieldName.Substring(0, lbIndex);
                    var subName = fieldName.Substring(rbIndex + 2);
                    var loopField = document.Fields.Find<IArray>(f =>
                        f.Name.Equals(loopName, StringComparison.OrdinalIgnoreCase));

                    if (loopField == null)
                    {
                        loopField = document.CreateArray(loopName,
                            isNumericLoop ? IteratorTypes.NumericRanges : IteratorTypes.Categorical);
                        // 初始化列表
                        var subVar = loopField.CreateVariable(subName, FieldDataType.Categorical);
                        // 检查fieldMap
                        var fullName = (loopName + "." + subName).ToLower();
                        fieldMap.TryAdd(fullName, subVar);
                    }

                    if (loopField.IteratorType == IteratorTypes.Categorical)
                    {
                        var code = Settings.GetCode(replaceValue, false);

                        if (loopField.Elements.Find<IElement>(e =>
                                string.Equals(e.Name, code, StringComparison.OrdinalIgnoreCase)) != null)
                        {
                            ReportService?.Warn($"分类元素'{code}'已存在。");
                        }
                        else
                        {
                            loopField.Elements.AddNew(Settings.GetCode(replaceValue, false));
                        }
                    }

                    if (isNumericLoop)
                    {
                        var lowerLoopName = loopName.ToLower();
                        if (!loopFieldRangeMap.TryGetValue(lowerLoopName,
                                out var fieldRangeInfo))
                        {
                            fieldRangeInfo = new MetadataFieldRangeInfo(loopField);
                            loopFieldRangeMap.Add(lowerLoopName, fieldRangeInfo);
                        }

                        if (innerIndex < fieldRangeInfo.Lower)
                        {
                            fieldRangeInfo.Lower = innerIndex;
                        }
                        else if (innerIndex > fieldRangeInfo.Upper)
                        {
                            fieldRangeInfo.Upper = innerIndex;
                        }
                    }
                }
                // normal field
                else
                {
                    var lowerFieldName = fieldName.ToLower();
                    if (!fieldMap.TryGetValue(lowerFieldName, out var normalField))
                    {
                        normalField = document.CreateField(fieldName,
                            item.IsLong ? FieldDataType.Long :
                            item.IsDouble ? FieldDataType.Double : FieldDataType.Categorical);
                        fieldMap.Add(lowerFieldName, normalField);
                    }
                }
            }

            if (fieldMap.Count > 1 && AutoMergeList)
            {
                var elementList = document.CreateElementList("list" + (document.Types.Count + 1));
                InitializeElements(elementList.Elements, item.Mapping);

                foreach (var variable in fieldMap.Values)
                {
                    variable.Elements.AddNew(elementList.Name, elementList.Id);
                }
            }
            else
            {
                foreach (var variable in fieldMap.Values)
                {
                    InitializeElements(variable.Elements, item.Mapping);
                }
            }

            foreach (var info in loopFieldRangeMap.Values)
            {
                info.ApplyRange();
            }
        }
    }

    private void CreateAppendFields(MetadataDocument document)
    {
        foreach (var field in Settings.AppendFields)
        {
            if (document.Fields.Find<IField>(f => f.Name.Equals(field.Name, StringComparison.OrdinalIgnoreCase)) !=
                null)
            {
                ReportService?.Error($"变量'{field.Name}'已存在。");
                continue;
            }

            var mdmField = document.CreateField(field.Name,
                field.Type == MetadataSqlFieldValueType.Double ? FieldDataType.Double :
                field.Type == MetadataSqlFieldValueType.Long ? FieldDataType.Long :
                field.Type == MetadataSqlFieldValueType.Text ? FieldDataType.Text :
                FieldDataType.Categorical);

            if (mdmField.DataType != FieldDataType.Categorical)
            {
                continue;
            }

            if (field.CodeMapping.Count > 0)
            {
                foreach (var pair in field.CodeMapping)
                {
                    var element = mdmField.Elements.AddNew(field.IsFixed ? pair.Key : Settings.Label + pair.Key);
                    element.Label.SetText(ExportLabelType, ExportContext, ExportLanguage, pair.Value);
                }
            }
            else
            {
                var nameSet = new HashSet<string>();
                foreach (var val in field.Values)
                {
                    var lowerValue = val.Value.ToLower();
                    if (nameSet.Contains(lowerValue))
                    {
                        continue;
                    }

                    var codeName = field.IsFixed ? val.Value : Settings.Label + val.Value;
                    mdmField.Elements.AddNew(codeName);
                    nameSet.Add(lowerValue);
                }
            }
        }
    }


    [MemberNotNull(nameof(_metadata), nameof(_provider), nameof(_parser))]
    private void PrepareMetadata()
    {
        if (_metadata != null)
        {
            _provider ??= new SqlMetadataProvider(_metadata, SqlFormat);
            _parser ??= new Parser();
            return;
        }

        _metadata = new MetadataDocument();
        _metadata.Contexts.AddNew(ExportContext);
        _metadata.Languages.AddNew(ExportLanguage);
        _metadata.LabelTypes.AddNew(ExportLabelType);

        CreateMetadata(_metadata);
        CreateAppendFields(_metadata);

        _provider ??= new SqlMetadataProvider(_metadata, SqlFormat);
        _parser ??= new Parser();

        Expression.MetadataProvider = _provider;
    }


    private class ColumnInfo
    {
        internal ColumnInfo(int column, string fieldName, MetadataCodeMapElement? mappedElement)
        {
            Column = column;
            FieldName = fieldName;
            SubFieldName = string.Empty;
            MappedElement = mappedElement;
            IsSingleCode = false;
        }

        /// <summary>
        ///     Excel Sheet中的列号，1开始。
        /// </summary>
        public int Column { get; }

        /// <summary>
        ///     映射前的字段名
        /// </summary>
        public string FieldName { get; }

        /// <summary>
        ///     映射后的下级字段，默认是空字符串。
        /// </summary>
        public string SubFieldName { get; init; }

        /// <summary>
        ///     当前列字段是否是单值字段。
        /// </summary>
        public bool IsSingleCode { get; init; }

        /// <summary>
        ///     检索到的映射对象。
        /// </summary>
        public MetadataCodeMapElement? MappedElement { get; }
    }

    private class MetadataFieldRangeInfo
    {
        internal MetadataFieldRangeInfo(IField field)
        {
            Field = field;
            Lower = -1;
            Upper = -1;
        }

        private IField Field { get; }

        public int Lower { get; set; }

        public int Upper { get; set; }

        public void ApplyRange()
        {
            Field.Ranges.Add(Upper, Lower);
        }
    }
}